package com.apexedu.eas.dao.impl;

import java.io.Serializable;
import java.util.List;
import java.util.Map;

import org.springframework.stereotype.Repository;

import com.apexedu.eas.annotation.Query;
import com.apexedu.eas.constants.Tbl;
import com.apexedu.eas.entity.Gxxs0101;
import com.apexedu.eas.entity.TjxJhjxb;
import com.apexedu.eas.entity.TjxJhjxbxs;
import com.apexedu.eas.entity.TjxMkxx;
import com.apexedu.eas.entity.TjxXkfa;
import com.apexedu.eas.entity.TjxXkfagz;
import com.apexedu.framework.util.DBUtil;
import com.apexedu.util.StringUtil;

@Repository
public class XkDao extends BasisDao<Gxxs0101, Serializable> {

	//根据学号查询学生
	@Query(value = "select * from Gxxs0101 where xh = ?")
	public Gxxs0101 getGxxs0101ByXh(String userId){
		return null;
	}
	
	//根据id查询学生
	@Query(value = "select * from  Gxxs0101  where id = ?")
	public Gxxs0101 getGxxs0101ById(Long id){
		return null;
	}
	
	//根据方案类型获取选课方案
	@Query(value = "select t.*, to_char(t.qdrq , 'yyyy-mm-dd') as qdrqmc, to_char(t.jsrq, 'yyyy-mm-dd') as jsrqmc  from tjx_xkfa t  where (t.zt=1 or t.zt = 2 or t.zt = 3) and falx=?")
	public TjxXkfa getXkfaByFalx(Integer falx){
		return null;
	}
	
	//根据选课方案获取对应选课方案规则的个数
	@Query(value = "select count(*) from @TjxXkfagz where xkfa = ?", replace=true, count=true)
	public Long getXkfagzNum (Long xkfaId){
		return null;
	}
	
	//根据选课方案获取对应的选课方案结果集
	@Query(value = "select * from @TjxXkfagz where xkfa = ?", replace=true)
	public List<TjxXkfagz> getXkfagzList(Long xkfaId){
		return null;
	}
	
	//获取公选课结果集
	/**
	 * 
	 * @param xkfa       选课方案
	 * @param xkfagzList 选课方案规则：sql语句的中间部分--不固定
	 * @param kcmc       搜索课程名称类似给定名称的课程
	 * @param xs         学生
	 * @return           返回一个封装了选修课结果集的List
	 */
	public List<Map<String, String>> getXxkList(TjxXkfa xkfa ,  List<TjxXkfagz> xkfagzList , String kcmc,  Gxxs0101 xs){
		StringBuilder xxksql = new StringBuilder();
		xxksql.append("select   "
				+"  tjx_jhjxb.id id,tjx_jhjxb.jxbmc jxbmc,  "
				+"  tjx_jhjxb.zkss zks,tjx_jhjxb.zxf zxf,  "
				+"  (select name from lborganization where id=tjx_jhjxb.cdxb) kkxb,  "
				+"  to_char((select wm_concat(xm) from tjx_jhjxbjs where jxb=tjx_jhjxb.id)) rkjs,  "
				+"  case when tjx_jhjxbxs.id is null then 0 else 1 end sfyx  "
				+"  from  "
				+"  (select * from tjx_jhjxb where sfqy=1 and xkfa=?) tjx_jhjxb  "
				+"  left join tjx_jhjxbxs on tjx_jhjxb.id=tjx_jhjxbxs.jxb and tjx_jhjxbxs.gxxs0101=? ,  "
				+"  (select * from gxxs0101 where id=?) gxxs0101  "
				+"  where  "
				+" instr(';'||TJX_JHJXB.MXZY||';',';'||GXXS0101.ZY||';')>0 "
				+" and "
				);
		int gzljfs = xkfa.getGzljfs();
		int size = xkfagzList.size();
		for (int i=0 ;i<size-1 ;i++) {
			xxksql.append(" ( "+xkfagzList.get(i).getGzyf()+" ) "+(gzljfs==2?"  and   ":"  or   "));
		}
		xxksql.append(" ( "+xkfagzList.get(size-1).getGzyf()+" ) ");
		if (StringUtil.isNotBlank(kcmc)) 
			xxksql.append(" and tjx_jhjxb.jxbmc like '%" + kcmc + "%'");
		xxksql.append(" order by jxbmc desc ");
		System.out.println("选课方案id："+xkfa.getId());
		System.out.println("学生id："+xs.getId());
		System.out.println(xxksql.toString());

		return easDBUtil.queryForList(xxksql.toString(), xkfa.getId(), xs.getId(),  xs.getId());
	}
	//获取板块课结果集
	/**
	 * 
	 * @param xkfa       选课方案
	 * @param xkfagzList 选课方案规则：sql语句的中间部分--不固定
	 * @param kcmc       搜索课程名称类似给定名称的课程
	 * @param xs         学生
	 * @return           返回一个封装了选修课结果集的List
	 */
	public List<Map<String, String>> getBkkList(TjxXkfa xkfa ,  List<TjxXkfagz> xkfagzList , String kcmc,  Gxxs0101 xs){
		StringBuilder xxksql = new StringBuilder();
		xxksql.append("select "
					+" tjx_jhjxb.id id,tjx_jhjxb.jxbmc jxbmc, "
					+" tjx_jhjxb.zkss zks,tjx_jhjxb.zxf zxf, "
					+" (select name from lborganization where id=tjx_jhjxb.cdxb) kkxb, "
					+" to_char((select wm_concat(xm) from tjx_jhjxbjs where jxb=tjx_jhjxb.id)) rkjs, "
					+" case when tjx_jhjxbxs.id is null then 0 else 1 end sfyx "
					+" from  "
					+" (select * from tjx_jhjxb where sfqy=1 and xkfa=?) tjx_jhjxb "
					+" left join TJX_BKXX on tjx_jhjxb.bkxx=tjx_bkxx.id "
					+" left join tjx_jhjxbxs on tjx_jhjxb.id=tjx_jhjxbxs.jxb and tjx_jhjxbxs.gxxs0101=?, "
					+" (select * from gxxs0101 where id=?) gxxs0101 "
					+" where "
					+" instr(';'||TJX_JHJXB.MXZY||';',';'||GXXS0101.ZY||';')>0 "
					+" and "
					) ;
		int gzljfs = xkfa.getGzljfs();
		int size = xkfagzList.size();
		for (int i=0 ;i<size-1 ;i++) {
			xxksql.append(" ( "+xkfagzList.get(i).getGzyf()+" ) "+(gzljfs==2?"  and   ":"  or   "));
		}
		xxksql.append(" ( "+xkfagzList.get(size-1).getGzyf()+" ) ");
		if (StringUtil.isNotBlank(kcmc)) 
			xxksql.append(" and tjx_jhjxb.jxbmc like '%" + kcmc + "%'");
		System.out.println("选课方案id："+xkfa.getId());
		System.out.println("学生id："+xs.getId());
		System.out.println(xxksql.toString());
		return easDBUtil.queryForList(xxksql.toString(), xkfa.getId(), xs.getId(),  xs.getId());
	}
	//获取模块课结果集
	/**
	 * 
	 * @param xkfa       选课方案
	 * @param xkfagzList 选课方案规则：sql语句的中间部分--不固定
	 * @param kcmc       搜索课程名称类似给定名称的课程
	 * @param xs         学生
	 * @return           返回一个封装了选修课结果集的List
	 */
	public List<Map<String, String>> getMkkList(TjxXkfa xkfa ,  List<TjxXkfagz> xkfagzList , String kcmc,  Gxxs0101 xs){
		StringBuilder xxksql = new StringBuilder();
		xxksql.append("select "
					+" TJX_MKXX.id id,TJX_MKKMB.Mkmc jxbmc, "
					+" tjx_jhxq.mc xqmc,tjx_kc_gxjx02.kcmc kcmc,TJX_MKXX_KCB.Wks wks,TJX_MKXX_KCB.ZKS zks, "
					+" case when TJX_MKKXKMD.id is null then 0 else 1 end sfyx, "
					+" TJX_MKXX.Bz bz "
					+" from "
					+" (select * from TJX_MKXX where xkfa=? ) TJX_MKXX "
					+" left join TJX_MKKXKMD on TJX_MKKXKMD.MKXX=TJX_MKXX.id and TJX_MKKXKMD.GXXS0101=?, "
					+" TJX_MKKMB,TJX_MKXX_KCB,tjx_jhxq,tjx_kc_gxjx02, "
					+" (select * from gxxs0101 where id=?) gxxs0101 "
					+" where  "
					+" TJX_MKKMB.id=TJX_MKXX.Glmb "
					+" and TJX_MKXX_KCB.TJX_MKXX_ID=TJX_MKXX.id "
					+" and tjx_jhxq.id=TJX_MKXX_KCB.Jhxq  "
					+" and tjx_kc_gxjx02.id=TJX_MKXX_KCB.Kc "
					+" and instr(';'||TJX_MKXX.mxzy||';',';'||GXXS0101.ZY||';')>0 "
					+" and "
					+" ( "
					) ;
		int gzljfs = xkfa.getGzljfs();
		int size = xkfagzList.size();
		for (int i=0 ;i<size-1 ;i++) {
			xxksql.append(" ( "+xkfagzList.get(i).getGzyf()+" ) "+(gzljfs==2?"  and   ":"  or   "));
		}
		xxksql.append(" ( "+xkfagzList.get(size-1).getGzyf()+" ) "+" ) ");
		if (StringUtil.isNotBlank(kcmc)) 
			xxksql.append(" and kcmc like '%" + kcmc + "%'");
		xxksql.append(" order by jxbmc ,xqmc");
		System.out.println("选课方案id："+xkfa.getId());
		System.out.println("学生id："+xs.getId());
		System.out.println(xxksql.toString());
		return easDBUtil.queryForList(xxksql.toString(), xkfa.getId(), xs.getId(),  xs.getId());
	}
	
	//根据教学班Id获取教学班
	@Query(value = "select * from  Tjx_Jhjxb  where id=?")
	public TjxJhjxb getJxbById (Long jxbid){
		return null;
	}
	
	//根据学生id和教学班id获取教学班学生信息
	@Query(value = "select * from  @TjxJhjxbxs  where gxxs0101=? and jxb=?", replace=true)
	public TjxJhjxbxs getJxbxs (Long xsid,Long jxbid){
		return null;
	}
	
	//获取学生公选课、板块课的选课门数
	@Query(value = "select count(xs.id)  from  @TjxJhjxbxs xs,Tjx_Jhjxb jx  where xs.jxb=jx.id and xs.gxxs0101=? and jx.xkfa=? ", replace=true, count=true)
	public Integer getXkms (Long xsid, Long xkfaid){
		return null;
	}
	
	//保存选课信息(公选课、板块课)
	@Query(value = "insert into @TjxJhjxbxs (id,jxb,gxxs0101,xh,xm) values (fnextid('@TjxJhjxbxs' ), ?, ?, ?, ?) ", replace=true, update=true)
	public Integer saveXkxx (Long jxbid , Long xsid ,String xh ,String xm){
		return null;
	}
	
	//获取当前教学班的学生数（公选课、板块课）
	@Query(value = "select count(*) from  @TjxJhjxbxs  where jxb = ?", replace=true, count=true)
	public Integer getJxbxsSum (Long jxbid){
		return null;
	}
	
	//删除教学班学生记录(公选课、板块课)
	@Query(value = "delete @TjxJhjxbxs a where a.gxxs0101=? and a.jxb=?", replace=true, update=true)
	public Integer deleteJxbxs (Long xsid ,Long jxbid){
		return null;
	}
	
	
	//根据班级id获取模块课信息
	@Query(value = "select * from  @TjxMkxx where id=?", replace=true)
	public TjxMkxx getMkxx (Long jxbid){
		return null;
	}
	
	//获取当前教学班的学生数（模块课）
	@Query(value = "select count(*) from  @TjxMkkxkmd  where mkxx = ?", replace=true, count=true)
	public Integer getMkkxsSum (Long jxbid){
		return null;
	}
	
	//获取学生模块课的选课门数
	@Query(value = "select count(xs.id) from @TjxMkkxkmd xs where xs.gxxs0101 = ?", replace=true, count=true)
	public Integer getMkkxkms (Long xsid){
		return null;
	}
	
	//保存选课信息（模块课）
	@Query(value = "insert into @TjxMkkxkmd (id, mkxx, gxxs0101, xh,xm) values (fnextid('@TjxMkkxkmd' ), ?, ?, ?, ?)", replace=true, update=true)
	public Integer saveMkkxkxx (Long jxbid,Long xsid,String xh,String xm){
		return null;
	}
	
	//删除教学班学生记录(模块课)
	@Query(value = "delete @TjxMkkxkmd a where a.gxxs0101=? and a.mkxx=?", replace=true, update=true)
	public Integer deleteMKKxs (Long xsid ,Long jxbid){
		return null;
	}
}
